﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using InstrumentCheck.Module;

namespace InstrumentCheck.DAL
{
    public class CorrectQuery_dal
    {
        public static  List<Item> getCorrectItem()
        {
            string sql = "select * from Correct_item a  inner join tbl_master b on a.Fas_NO=B.ast_no";
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, null);
            List<Item> CorrectItem = new List<Item>();
            foreach(DataRow row  in ds.Tables[0].Rows)
            {
                Item item = new Item();
                item.AssetNO = row["Fas_NO"].ToString();
                item.ChiName = row["Chi_name"].ToString();
                item.Spec = row["spec"].ToString();
                item.ModelNO = row["Model_no"].ToString();
                item.Brand = row["brand"].ToString();
                CorrectItem.Add(item);
            }
            return CorrectItem;
         
        }
        public static List<QueryItem> StatusCount(string QueryCondition)
        {
            int i=0;
            string DeptNO="";
            Boolean flag = false;
            QueryItem item = new QueryItem();
            List<QueryItem> queryItem = new List<QueryItem>();
            //string sql = " select count(status) as StatusCount, substring(KeepDept,0,4) as DeptNO,status";
            string sql = "select *,substring(KeepDept, 0, 4) as DeptNO ";
            sql += " from (select a.correct_date,a.Correct_period,a.PR_NO,a.fas_type ,a.fas_no,a.Correct_result,a.status,b.chi_name,";
            sql += " b.spec,b.user_name as Keeper,b.dept_no as KeepDept, b.brand,b.user_id,b.model_no";
            sql += " from correct_item a inner join  tbl_master b on a.fas_no=b.ast_no union";
            sql += " select a.correct_date,a.Correct_period,a.PR_NO,a.fas_type , a.fas_no,a.Correct_result,a.status,b.chi_name,";
            sql += " b.spec,b.user_name as Keeper,b.dept_no as KeepDept, b.brand,b.user_id,b.model_no";
            sql += " from correct_item a inner join tbl_control b on a.fas_no=b.ast_no ) a";
            sql += " where 1=1";
             sql += QueryCondition;
             //sql += " group by  substring(KeepDept,0,4),status";
             sql += " order by DeptNO,status";
             SqlDataAdapter ada = new SqlDataAdapter();
             DataSet ds = new DataSet();
             SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, null);
             if (ds.Tables[0].Rows.Count == 0)
                 return null;
             else
             {
                 foreach (DataRow row in ds.Tables[0].Rows)
                 {
                     if(i==0)
                         DeptNO = row["DeptNO"].ToString();
                     if (row["DeptNO"].ToString() == DeptNO) //判斷是否是同一部門,
                     {
                         AddQueryItem(row, item);
                         flag = false;
                     }
                     else
                     {
                         queryItem.Add(item);
                         item = new QueryItem();
                         DeptNO = row["DeptNO"].ToString();
                         flag = true;
                     }
                     if (flag)
                         AddQueryItem(row, item);
                     i++;
                     //item.DeptNO = row["DeptNO"].ToString();
                     //item.Status = int.Parse(row["status"].ToString());
                     //item.StatusCount = int.Parse(row["StatusCount"].ToString());
                     //queryItem.Add(item);
                 }
                 queryItem.Add(item);
                 getTotalCount(queryItem);
                 return queryItem;
             }
            
        }
        protected static DateTime  correctdate(DataRow row)
        {
            DateTime correctdate;
            int period=0;
            switch ((CorrectCycle)row["correct_period"])
            { 
                case CorrectCycle.HalfYear:
                    period = 6;
                    break;
                case CorrectCycle.OneYear :
                    period = 12;
                    break;
                case CorrectCycle.TwoYears :
                    period = 24;
                    break;
                default :
                    break;
            }
            correctdate = DateTime.Parse(row["correct_date"].ToString()).AddMonths(period);
            return correctdate;
        }
        protected static void getTotalCount(List<QueryItem> queryItem)
        {
            QueryItem item = new QueryItem();
            foreach (QueryItem tmpItem in queryItem)
            {
                item.AgainstCorrectNum += tmpItem.AgainstCorrectNum;
                item.AlreadyCorrectNum += tmpItem.AlreadyCorrectNum;
                item.DeptNO = "Total:";
                item.ForbidenUseNum += tmpItem.ForbidenUseNum;
                item.HavenotCorrectNum += tmpItem.HavenotCorrectNum;
                item.NeedCorrectNum += tmpItem.NeedCorrectNum;
                item.PauseInUseNum += tmpItem.PauseInUseNum;
                item.UselessNum += tmpItem.UselessNum;
               
            }
            queryItem.Add(item);
           
        }
        public static void AddQueryItem(DataRow row, QueryItem item)
        {

            item.DeptNO = row["DeptNO"].ToString();
            switch ((AssetStatus)row["status"])
            {
                case AssetStatus.AgainstCorrect:
                    //item.AgainstCorrectNum = int.Parse(row["StatusCount"].ToString());
                    item.AgainstCorrectNum += 1;
                    break;
                //case AssetStatus.AlreadyCorrect:
                //    if (correctdate(row) >= DateTime.Now)
                //        //item.AlreadyCorrectNum = int.Parse(row["StatusCount"].ToString());
                //        item.AlreadyCorrectNum += 1;
                //    break;
                case AssetStatus.ForbidUse:
                    //item.ForbidenUseNum = int.Parse(row["StatusCount"].ToString());
                    item.ForbidenUseNum += 1;
                    break;
                //case AssetStatus.HaventCorrect:
                //    if(correctdate(row)<DateTime.Now)
                //      //item.HavenotCorrectNum = int.Parse(row["StatusCount"].ToString());
                //        item.HavenotCorrectNum += 1;
                //    break;
                case AssetStatus.NeedCorrect:
                    //item.NeedCorrectNum = int.Parse(row["StatusCount"].ToString());
                    item.NeedCorrectNum += 1;
                    if (correctdate(row) >= DateTime.Now)
                        item.AlreadyCorrectNum += 1;
                    if (correctdate(row) < DateTime.Now)
                        item.HavenotCorrectNum += 1;
                    break;
                case AssetStatus.PauseInUse:
                   // item.PauseInUseNum = int.Parse(row["StatusCount"].ToString());
                    item.PauseInUseNum += 1;
                    break;
                case AssetStatus.Useless:
                    //item.UselessNum = int.Parse(row["StatusCount"].ToString());
                    item.UselessNum += 1;
                    break;
                default:
                    break;
            }
            
        }
        public static List<ApplyForm> getCorrecyApplyForm(string Condition)
        {
            List<ApplyForm> applyform = new List<ApplyForm>();
            string sql = "select * from ";
            sql += "(select a.PR_NO,a.fas_type , a.Number,a.FormStatus,a.fas_no,a.Correct_result,a.status,b.chi_name,";
            sql += " b.spec,b.user_name as Keeper,b.dept_no as KeepDept, b.brand,b.user_id,b.model_no";
            sql += " from correct a inner join  tbl_master b on a.fas_no=b.ast_no union";
            sql += " select a.PR_NO,a.fas_type , a.Number,a.FormStatus,a.fas_no,a.Correct_result,a.status,b.chi_name,";
            sql += " b.spec,b.user_name as Keeper,b.dept_no as KeepDept, b.brand,b.user_id,b.model_no";
            sql += " from correct a inner join tbl_control b on a.fas_no=b.ast_no ) a";
            sql += " where FormStatus <>0 ";
            if (Condition != "")
                sql += Condition;
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count <= 0)
                return null;
            else
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                     applyform.Add(AddApplyForm(row));
                
            }
            return applyform;
         
        }
        public static ApplyForm AddApplyForm(DataRow row)
        {
            ApplyForm form = new ApplyForm();
            form.FormNO =int.Parse(row["Number"].ToString());
            Item item = new Item();
            item.AssetNO = row["fas_no"].ToString();
            item.ChiName = row["chi_name"].ToString();
            if(row["Correct_result"].ToString() !="")
               item.CorrectResult = (CorrectResult)row["Correct_result"];
            item.CurrentStatus = (AssetStatus)row["status"];
            item.Spec = row["spec"].ToString();
            form.FormStatus = (FormStatus)row["FormStatus"];
            User user = new User();
            user.DeptNO = row["KeepDept"].ToString();
            user.EmpChineseName = row["keeper"].ToString();
            form.Item = item;
            form.Creater = user;
            return form;
   
        }
        public static List<Attach> getAttach(string FasNO)
        {
            List<Attach> attach = new List<Attach>();
            string sql = " select SystemName,ClientName from Tbl_Attachment_Active where Fas_NO=@Fas_NO";
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SqlParameter[] par = new SqlParameter[1];
            par[0]= SQLHelper.SQLHelper.CreateParameter("Fas_NO", SqlDbType.NVarChar,FasNO, ParameterDirection.Input);
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, par);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Attach tmpAttach = new Attach();
                    tmpAttach.SystemName = row["SystemName"].ToString();
                    tmpAttach.ClientName = row["ClientName"].ToString();
                    attach.Add(tmpAttach);
                }
            }
            return attach;

        }
 
        public static List<User> getCorrectDept()
        {
            string sql = " select distinct *  from ";
            sql += " (select dept_no ,dept_name from tbl_master a,Correct b where a.ast_no = b.fas_no";
            sql += " union select dept_no ,dept_name from tbl_control a,Correct b where a.ast_no = b.fas_no) a";

            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, null);
            List<User> CorrectItem = new List<User>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                
                User keeper = new User();
                keeper.DeptNO = row["dept_no"].ToString();
                //keeper.DeptNO = row["dept_name"].ToString();
                CorrectItem.Add(keeper); 
            }
            return CorrectItem;
        }
        public static string Keeper(Item item)
        {
            AssetType type = item.AssetType;
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            string sql="";
            switch (type)
            { 
                case AssetType.FixedAsset :
                    sql = " select * from tbl_master where ast_no=@fasno";
                    break;
                case AssetType.RestrictedItems :
                    sql = " select * from tbl_control where ast_no=@fasno";
                    break;
                default :
                    break;
            }
            SqlParameter[] par = new SqlParameter[1];
            par[0] = SQLHelper.SQLHelper.CreateParameter("fasno", SqlDbType.NVarChar, item.AssetNO, ParameterDirection.Input);
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, par);
            return ds.Tables[0].Rows[0]["user_id"].ToString();
        }
        public static void UpdateAlertList(Item item)
        {
            string sql = "update alert_list set USER_Response='Y' WHERE fas_no =@fasno and correct_date = @date";
            SqlParameter[] par = new SqlParameter[2];
            par[0] = SQLHelper.SQLHelper.CreateParameter("fasno", SqlDbType.NVarChar, item.AssetNO, ParameterDirection.Input);
            par[1] = SQLHelper.SQLHelper.CreateParameter("date", SqlDbType.DateTime, item.NeedCorrectDate, ParameterDirection.Input);
            SQLHelper.SQLHelper.ExecuteNonQuery(CommandType.Text, sql, par);
        }
        
    }
}
